/*
 * sqlhandle.cpp
 *
 *  Created on: Dec 9, 2010
 *      Author: khanton
 */

#include <iostream>
#include <query.h>
#include <connection.h>

#include "sqlhandle.h"
#include "format.h"
#include "log.h"
#include "controllers_network.h"
#include "options.h"

sqlhandle::sqlhandle()
{

}

sqlhandle::~sqlhandle()
{
	close();
	delete pool_;
}

void sqlhandle::start(const std::string &_host, const std::string &_user, const std::string &_password, const std::string &_database)
{
	pool_ = new asud_connection_pool(_host, _user, _password, _database);
}

void sqlhandle::close()
{
}

bool sqlhandle::create_controller_on_network(const con_state::controller_id _controller, std::string &_key)
{
	mysqlpp::Connection *connection = NULL;
	
	bool ret = true;

	try
	{
		connection = pool_->safe_grab();
		
		mysqlpp::Transaction trans(*connection);
		
		mysqlpp::Query query(connection);
		
		query << "select c_id, enc_key from network_map where c_id=" << _controller;

		dump_query(query.str());
			
		if (mysqlpp::UseQueryResult res = query.use())
		{
			if (mysqlpp::Row row = res.fetch_row())
			{
			  _key = std::string(row["enc_key"]);
			}  
			else
			{
			  throw std::logic_error("No controler");
			}
		}
		else
		{
		  throw std::logic_error("No controler");
		}
		
		query << "delete from c_status where c_id=" << _controller;
		
		dump_query(query.str());
		
		query.execute();

		time_t now;

		time(&now);

		query << "insert into c_status set c_id=" << _controller << ", connect_time=FROM_UNIXTIME(" << now << ")";

		dump_query(query.str());

		query.execute();
		
		trans.commit();

	}
	catch(const std::exception &_error)
	{
	    g_log.msg(MSG_NORMAL, format("Can not load controller network: %1").arg(_error.what()));
	    ret = false;
	}

	if (connection)
		pool_->release(connection);
		
	return ret;		
}

void sqlhandle::delete_controller_from_network(const con_state::controller_id _controller)
{

	mysqlpp::Connection *connection = NULL;
	
	try
	{
		connection = pool_->safe_grab();

		std::string query_text = (format("delete from c_status where c_id=%1").num(_controller)).str();

		mysqlpp::Query query(connection->query(query_text));

		dump_query(query.str());

		query.execute();

	}
	catch(const std::exception &_error)
	{
	    g_log.msg(MSG_NORMAL, format("Can not load controller network: %1").arg(_error.what()));
	}

	if (connection)
	{
		pool_->release(connection);
	}
}

void sqlhandle::dump_query(const std::string &_query)
{
	if (g_config.m_dump_all_query)
	{
	  g_log.msg(MSG_NORMAL, format("SQL QUERY:%1").arg(_query));
	}
}

void sqlhandle::set_controller_state(con_state::controller_id _id, const con_state::status_packet_t &_con_state)
{
	mysqlpp::Connection *connection = NULL;
	
	try
	{
		con_state state;

		state = _con_state;
	
		tm ctime = boost::posix_time::to_tm(state.as_local_time()); // here we can get bad date exception
																	
		time_t cont_time = mktime(&ctime);
		time_t chg_time;
		time(&chg_time);

		std::string query_text = (format("update c_status set flash_status=%1, job_mode=%2, program=%3,"
					  "phase=%4, fault=%5, fault_red=%6, fault_yellow=%7,"
					  "fault_green=%8, no_program=%9,cont_local_time=FROM_UNIXTIME(%10), cont_scan_time=FROM_UNIXTIME(%11) where c_id=%12").
					  num(_con_state.flash_status_).num(_con_state.job_mode_).num(_con_state.current_program_)
					  .num(_con_state.current_phase_).num(_con_state.fault_type_).num(_con_state.fault_red_)
					  .num(_con_state.fault_yellow_).num(_con_state.fault_green_).num(_con_state.no_programm_)
					  .time(cont_time).time(chg_time).num(_id)).str();

		connection = pool_->safe_grab();
		
		mysqlpp::Transaction trans(*connection);

		mysqlpp::Query query(connection->query(query_text));

		dump_query(query.str());

		query.execute();
		
		if (query.affected_rows() == 0)
		{
		  time_t now;

		  time(&now);
		  
		  std::string ins_query = (format("insert into c_status set c_id=%1, connect_time=FROM_UNIXTIME(%2), "
					  "flash_status=%3, job_mode=%4, program=%5, phase=%6, fault=%7, fault_red=%8, fault_yellow=%9,"
					  "fault_green=%10, no_program=%11,cont_local_time=FROM_UNIXTIME(%12), cont_scan_time=FROM_UNIXTIME(%13)")
					  .num(_id).time(now).num(_con_state.flash_status_).num(_con_state.job_mode_)
					  .num(_con_state.current_program_).num(_con_state.current_phase_).num(_con_state.fault_type_)
					  .num(_con_state.fault_red_).num(_con_state.fault_yellow_).num(_con_state.fault_green_).num(_con_state.no_programm_)
					  .time(cont_time).time(chg_time)).str();

		  query << ins_query;

		  dump_query(query.str());

		  query.execute();
		}
		
//		g_log.msg(MSG_NORMAL, format("updated %1 rows").num(query.affected_rows()));
		
		trans.commit();
	}
	catch(const std::exception &_error)
	{
	    g_log.msg(MSG_NORMAL, format("set_controller_state: id=%1, status='Can not update controller error: %2'").num(_id).arg(_error.what()));
	}

	if (connection)
	{
		pool_->release(connection);
	}
}

bool sqlhandle::get_queue_cmd(int &_command, con_state::controller_id &_id, int &_param)
{
	mysqlpp::Connection *connection = NULL;

	bool ret = true;

	try
	{
		connection = pool_->safe_grab();

		mysqlpp::Transaction trans(*connection);

		mysqlpp::Query query(connection);

		query << "select uid, c_id, cmd, param from cmd_queue limit 1";

		std::string uid;

		dump_query(query.str());

		if (mysqlpp::UseQueryResult res = query.use())
		{
			if (mysqlpp::Row row = res.fetch_row())
			{
			  uid = std::string(row["uid"]);
			  _command = int(row["cmd"]);
			  _id = int(row["c_id"]);

			  try
			  {
			  _param = int(row["param"]);
			  }
			  catch (const std::exception &_error)
			  {
				  _param= 0;
			  }

			}
			else
			{
			  throw std::logic_error("No data");
			}
		}
		else
		{
		  throw std::logic_error("No data");
		}

		if (uid.empty())
		{
			return true;
		}

		query << "delete from cmd_queue where uid=\'" << uid << "\'" ;

		dump_query(query.str());

		query.execute();

		trans.commit();

	}
	catch(const std::exception &_error)
	{
	    ret = false;
	}

	if (connection)
		pool_->release(connection);

	return ret;

}

bool sqlhandle::get_controller_program(const con_state::controller_id _id, std::string &_program)
{
	mysqlpp::Connection *connection = NULL;

	bool ret = true;

	try
	{
		connection = pool_->safe_grab();
		
		mysqlpp::Transaction trans(*connection);

		mysqlpp::Query query(connection);

		query << "select program from network_map where c_id=" << _id;

		std::string uid;

		dump_query(query.str());

		if (mysqlpp::UseQueryResult res = query.use())
		{
			if (mysqlpp::Row row = res.fetch_row())
			{
			  _program = std::string(row["program"]);
			}
			else
			{
			  throw std::logic_error("No data");
			}
		}
		else
		{
		  throw std::logic_error("No data");
		}

		if (_program.empty())
		{
			return true;
		}
	}
	catch(const std::exception &_error)
	{
	    ret = false;
	}

	if (connection)
		pool_->release(connection);

	return ret;

}

bool sqlhandle::start_program_update(const con_state::controller_id _id)
{
	mysqlpp::Connection *connection = NULL;

	bool ret = true;

	try
	{
		connection = pool_->safe_grab();
		
		mysqlpp::Transaction trans(*connection);

		mysqlpp::Query query(connection);
		
		query << "select prog_update_status from network_map where c_id=" << _id;

		std::string uid;

		dump_query(query.str());
		
		if (mysqlpp::UseQueryResult res = query.use())
		{
			if (mysqlpp::Row row = res.fetch_row())
			{
			  int prog_status = int(row["prog_update_status"]);
			  
			  if (prog_status == 1)
			  {
				throw std::logic_error("Invalid status");
			  }
			}
			else
			{
			  throw std::logic_error("No data");
			}
		}
		else
		{
		  throw std::logic_error("No data");
		}

		query << "update network_map set prog_update_status=1 where c_id=" << _id ;

		dump_query(query.str());

		query.execute();

		trans.commit();
		
		ret = true;

	}
	catch(const std::exception &_error)
	{
	    ret = false;
	}

	if (connection)
		pool_->release(connection);

	return ret;
}

bool sqlhandle::end_program_update(const con_state::controller_id _id, bool _success)
{
	mysqlpp::Connection *connection = NULL;

	bool ret = true;

	try
	{
		connection = pool_->safe_grab();
		
		mysqlpp::Transaction trans(*connection);

		mysqlpp::Query query(connection);
		
		if (_success)
		{
		  query << "update network_map set prog_update_status=3, prev_enc_key=enc_key, enc_key=next_enc_key, prog_update_time=NOW() where c_id=" << _id;
		}
		else
		{
		  query << "update network_map set prog_update_status=4 where c_id=" << _id ;
		}  

		dump_query(query.str());

		query.execute();

		trans.commit();
		
		ret = true;

	}
	catch(const std::exception &_error)
	{
	    ret = false;
	}

	if (connection)
		pool_->release(connection);

	return ret;
}

